﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Data.OleDb;
using System.Data;
using System.Collections;
using System.IO;

public class ExcelHelper
{

    /// <summary>
    /// 初始化ExcelHelper
    /// </summary>
    public ExcelHelper()
    {

    }

    /// <summary>
    /// 初始化ExcelHelper
    /// </summary>
    /// <param name="excelPath">Excel文件路径</param>
    public ExcelHelper(string excelPath)
    {
        this.ExcelPath = excelPath;
    }

    private string _ExcelPath = string.Empty;

    /// <summary>
    /// Excel文件路径
    /// </summary>
    public string ExcelPath
    {
        get
        {
            if (string.IsNullOrWhiteSpace(_ExcelPath))
            {
                throw new ArgumentNullException(_ExcelPath);
            }

            if (!File.Exists(_ExcelPath))
            {
                throw new FileNotFoundException("文件未找到！", _ExcelPath);
            }

            return _ExcelPath;
        }
        set
        {
            _ExcelPath = value;
        }
    }


    /// <summary>
    /// 返回 打开的 OleDbConnection
    /// </summary>
    private OleDbConnection Conn
    {
        get
        {
            OleDbConnection _conn = GetConnection(ExcelPath);
            _conn.Open();
            return _conn;
        }
    }

    /// <summary>
    /// 获取Oledb连接对象
    /// </summary>
    /// <param name="excelFile">Excel文件路径</param>
    /// <returns></returns>
    private static OleDbConnection GetConnection(string excelFile)
    {
        string ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + excelFile + ";Extended Properties='Excel 8.0;IMEX=2;HDR=YES'";
        if (excelFile.ToLower().IndexOf(".xlsx") > 0)
        {
            ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + excelFile + ";Extended Properties='Excel 12.0;IMEX=2;HDR=YES'";
        }
        return new OleDbConnection(ConnectionString);
    }

    #region 非静态方法

    /// <summary>
    /// 获取Excel中Sheet表名
    /// </summary>
    public List<string> GetSheetNames()
    {
        List<string> lstSheetNames = new List<string>();
        DataTable data = null;
        try
        {
            //获取所有表名称
            data = Conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "Table" });
            if (data == null)
            {
                return new List<string>();
            }
            else
            {
                foreach (DataRow dr in data.Rows)
                {
                    object sheetName = dr["TABLE_NAME"] ?? string.Empty;

                    if (sheetName.ToString().Contains("_xlnm#_FilterDatabase"))
                    {
                        continue;
                    }
                    // SheetName 为数字时，将 单引号去除
                    lstSheetNames.Add(sheetName.ToString().Replace("$", "").Replace("'", ""));
                }
            }
        }
        catch (Exception ex)
        {
            throw ex;
        }
        finally
        {
            if (Conn != null)
            {
                Conn.Close();
                Conn.Dispose();
            }
        }
        return lstSheetNames;
    }


    /// <summary>
    /// 获取Sheet表数据
    /// </summary>
    public DataTable GetSheetData(string sheetName, string selectColumns = "*", string whereSheet = "")
    {
        DataTable data = new DataTable();
        try
        {
            sheetName = sheetName.IndexOf("$") > -1 ? sheetName : sheetName + "$";
            string cmdText = "select " + selectColumns + " from [{0}] where 1=1 " + (string.IsNullOrEmpty(whereSheet) ? "" : whereSheet);
            cmdText = string.Format(cmdText, sheetName);
            OleDbDataAdapter adapter = new OleDbDataAdapter(cmdText, Conn);
            adapter.Fill(data);
        }
        catch (Exception ex)
        {
            throw ex;
        }
        finally
        {
            if (Conn != null)
            {
                Conn.Close();
                Conn.Dispose();
            }
        }
        return data;
    }





    /// <summary>
    /// 获取Excel中所有Sheet表数据
    /// </summary>
    /// <param name="excelFile">Excel文件路径</param>
    /// <returns></returns>
    public Dictionary<string, DataTable> GetAllSheetData()
    {
        Dictionary<string, DataTable> toReturn = new Dictionary<string, DataTable>();
        string tableNametoFill = string.Empty;
        string selectCommand = string.Empty;
        try
        {
            DataTable dt = Conn.GetSchema("Tables");
            foreach (DataRow row in dt.Rows)
            {
                tableNametoFill = row["TABLE_NAME"].ToString();
                selectCommand = string.Format("SELECT * FROM [{0}]", tableNametoFill.Trim().Replace("'", ""));
                OleDbDataAdapter oleda = new OleDbDataAdapter(selectCommand, Conn);
                DataTable dtData = new DataTable();
                oleda.Fill(dtData);
                toReturn.Add(tableNametoFill.Replace("'", ""), dtData);
            }
            return toReturn;
        }
        catch (Exception ex)
        {
            throw ex;
        }
        finally
        {
            if (Conn != null)
            {
                Conn.Close();
                Conn.Dispose();
            }
        }
    }

    /// <summary>
    /// 更加SheetName 获取 所有列名
    /// </summary>
    /// <param name="excelFile"></param>
    /// <param name="sheetName"></param>
    /// <returns></returns>
    public List<string> GetColumnNames(string sheetName)
    {
        List<string> lstColumnName = new List<string>();
        DataTable schemaTable = new DataTable();
        try
        {
            sheetName = sheetName.IndexOf("$") > -1 ? sheetName : sheetName + "$";
            schemaTable = Conn.GetOleDbSchemaTable(OleDbSchemaGuid.Columns,
                new Object[] { null, null, sheetName, null });
            foreach (DataRow row in schemaTable.Rows)
            {
                lstColumnName.Add(row[3].ToString());
            }
        }
        catch (Exception ex)
        {
            throw ex;
        }
        finally
        {
            if (Conn != null)
            {
                Conn.Close();
                Conn.Dispose();
            }
        }
        return lstColumnName;
    }


    public int ExecuteNonQuery(string strSql)
    {
        var cmd = Conn.CreateCommand();
        try
        {
            cmd.CommandText = strSql;
            int res = cmd.ExecuteNonQuery();
            return res;
        }
        catch (Exception ex)
        {
            throw ex;
        }
        finally
        {
            if (Conn != null)
            {
                Conn.Close();
                Conn.Dispose();
            }
        }
    }


    #endregion

    #region 静态方法

    public static DataTable CreateDataTable(params string[] columnNames)
    {
        DataTable data = new DataTable();
        if (columnNames != null && columnNames.Length > 0)
        {
            data.Columns.AddRange(columnNames.Select(t => new DataColumn(t)).ToArray());
        }
        return data;
    }


    /// <summary>
    /// 导出Excel
    /// </summary>
    /// <param name="Table">DataTable数据</param>
    /// <param name="ExcelFilePath">导出Excel文件路径（存在则删除）</param>
    /// <returns></returns>
    public static bool OutputToExcel(DataTable Table, string ExcelFilePath)
    {
        if (File.Exists(ExcelFilePath))
        {
            File.Delete(ExcelFilePath);
        }

        if ((Table.TableName.Trim().Length == 0) || (Table.TableName.ToLower() == "table"))
        {
            Table.TableName = "Sheet1";
        }

        //数据表的列数
        int ColCount = Table.Columns.Count;

        //用于记数，实例化参数时的序号
        int i = 0;

        //创建参数
        OleDbParameter[] para = new OleDbParameter[ColCount];

        //创建表结构的SQL语句
        string TableStructStr = @"Create Table " + Table.TableName + "(";

        //连接字符串
        string ConnString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + ExcelFilePath + ";Extended Properties=Excel 8.0;";
        OleDbConnection objConn = new OleDbConnection(ConnString);

        //创建表结构
        OleDbCommand objCmd = new OleDbCommand();

        //数据类型集合
        ArrayList DataTypeList = new ArrayList();
        DataTypeList.Add("System.Decimal");
        DataTypeList.Add("System.Double");
        DataTypeList.Add("System.Int16");
        DataTypeList.Add("System.Int32");
        DataTypeList.Add("System.Int64");
        DataTypeList.Add("System.Single");

        //遍历数据表的所有列，用于创建表结构
        foreach (DataColumn col in Table.Columns)
        {
            //如果列属于数字列，则设置该列的数据类型为double
            if (DataTypeList.IndexOf(col.DataType.ToString()) >= 0)
            {
                para[i] = new OleDbParameter("@" + col.ColumnName, OleDbType.Double);
                objCmd.Parameters.Add(para[i]);

                //如果是最后一列
                if (i + 1 == ColCount)
                {
                    TableStructStr += col.ColumnName + " double)";
                }
                else
                {
                    TableStructStr += col.ColumnName + " double,";
                }
            }
            else
            {
                para[i] = new OleDbParameter("@" + col.ColumnName, OleDbType.VarChar);
                objCmd.Parameters.Add(para[i]);

                //如果是最后一列
                if (i + 1 == ColCount)
                {
                    TableStructStr += col.ColumnName + " varchar)";
                }
                else
                {
                    TableStructStr += col.ColumnName + " varchar,";
                }
            }
            i++;
        }

        //创建Excel文件及文件结构
        try
        {
            objCmd.Connection = objConn;
            objCmd.CommandText = TableStructStr;

            if (objConn.State == ConnectionState.Closed)
            {
                objConn.Open();
            }
            objCmd.ExecuteNonQuery();
        }
        catch (Exception exp)
        {
            throw exp;
        }

        //插入记录的SQL语句
        string InsertSql_1 = "Insert into " + Table.TableName + " (";
        string InsertSql_2 = " Values (";
        string InsertSql = "";

        //遍历所有列，用于插入记录，在此创建插入记录的SQL语句
        for (int colID = 0; colID < ColCount; colID++)
        {
            if (colID + 1 == ColCount)  //最后一列
            {
                InsertSql_1 += Table.Columns[colID].ColumnName + ")";
                InsertSql_2 += "@" + Table.Columns[colID].ColumnName + ")";
            }
            else
            {
                InsertSql_1 += Table.Columns[colID].ColumnName + ",";
                InsertSql_2 += "@" + Table.Columns[colID].ColumnName + ",";
            }
        }

        InsertSql = InsertSql_1 + InsertSql_2;

        //遍历数据表的所有数据行
        for (int rowID = 0; rowID < Table.Rows.Count; rowID++)
        {
            for (int colID = 0; colID < ColCount; colID++)
            {
                if (para[colID].DbType == DbType.Double && Table.Rows[rowID][colID].ToString().Trim() == "")
                {
                    para[colID].Value = 0;
                }
                else
                {
                    para[colID].Value = Table.Rows[rowID][colID].ToString().Trim();
                }
            }
            try
            {
                objCmd.CommandText = InsertSql;
                objCmd.ExecuteNonQuery();
            }
            catch (Exception exp)
            {
                string str = exp.Message;
            }
        }
        try
        {
            if (objConn.State == ConnectionState.Open)
            {
                objConn.Close();
            }
        }
        catch (Exception exp)
        {
            throw exp;
        }
        return true;
    }
    #endregion
}